package com.raychi.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.raychi.demo.entity.Teacher;

public class TeacherDao {
	/**
	 * 通过id获取老师的所有信息
	 * @param conn 连接数据库
	 * @param id 页面传入的id
	 * @return 指定id的老师信息表单
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Teacher getTeacherById(Connection conn, int id) throws ClassNotFoundException, SQLException{
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery("select * from Teacher where id=" + id);
		Teacher teacher = null;
		while(rs.next()){
			teacher = createTeacher(rs);
		}
		return teacher;
	}
	
	/**
	 * 通过用户名和密码从数据库中获得老师的信息
	 * @param conn
	 * @param username 输入的用户名
	 * @param password 输入的密码
	 * @return 该老师信息表
	 * @throws SQLException
	 */
	public Teacher getTeacherByUsernameAndPassword(Connection conn, String username, String password) throws SQLException {
		String sql = "select * from Teacher where username=? and password=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, username);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();
		Teacher teacher = null;
		while (rs.next()) {
			teacher = createTeacher(rs);
		}
		return teacher;
	}
	
	/**
	 * 创建一个老师
	 * @param rs 从数据库获得的老师的信息
	 * @return 老师信息的列表
	 * @throws SQLException
	 */
	private static Teacher createTeacher(ResultSet rs) throws SQLException {
		Teacher teacher = new Teacher();
		teacher.setName(rs.getString("name"));
		teacher.setId(rs.getInt("id"));
		return teacher;
	}
	
	/**
	 * 将注册成功的老师存入数据库
	 * @param teacher 注册页面输入的信息
	 * @param conn 连接数据库
	 * @throws Exception
	 */
	public void save(Teacher teacher, Connection conn) throws Exception{
		String sql = "insert into Teacher (name, username,password) values(?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, teacher.getName());
		ps.setString(2, teacher.getUsername());
		ps.setString(3, teacher.getPassword());
		ps.execute();
	}
	
	/**
	 * 通过输入的用户名来查询并且统计数量
	 * @param conn 连接数据库
	 * @param username 注册时输入的用户名
	 * @return 如果有则rsl为1  没有则为0
	 * @throws SQLException
	 */
	public int getCountTeacherByUsername(Connection conn, String username) throws SQLException {
		String sql = "select count(*) from Teacher where username='" + username + "'";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		int rsl = 0;
		while (rs.next()) {
			rsl = rs.getInt(1);
		}
		return rsl;
	}
	
	
}
